8  Working with Time

8.1 Reference

Default database in this chapter is “ap”.

Other databases included in the simulation MySQL server:

  • om
  • ex

8.2 Data Type

5 Different temporal data types:

  • Time (2:22:37)
  • DateTime (2028-10-15 2:22:37)
  • TimeStamp (2028-10-15 2:22:37 UTC)
  • Date (2028-10-15)
  • Year (2028)

Note: Value stored in the database and displayed value are 2 different things.

For example, all of the following displayed dates have the same stored values in the database:

  • ‘2028-08-15’
  • ‘2028-8-15’
  • ‘28-8-15’
  • ‘20280815’

All of these stored as 2028-8-15 in the database

8.3 Working with Time

Get Current Date and Time

For more information about UTC (Coordinated Universal Time).

UTC

SELECT 
  NOW(),
  SYSDATE(),
  CURRENT_TIMESTAMP(),
  CURDATE(),
  CURRENT_DATE(),
  CURTIME(),
  CURRENT_TIME(),
  UTC_DATE(),
  UTC_TIME()
1 records
NOW() SYSDATE() CURRENT_TIMESTAMP() CURDATE() CURRENT_DATE() CURTIME() CURRENT_TIME() UTC_DATE() UTC_TIME()
2024-11-09 13:53:24 2024-11-09 13:53:24 2024-11-09 13:53:24 2024-11-09 2024-11-09 13:53:24 13:53:24 2024-11-09 18:53:24

8.4 Extract Time Component

SELECT
  invoice_due_date,
  DAYOFMONTH(invoice_due_date),
  MONTH(invoice_due_date),
  YEAR(invoice_due_date),
  HOUR(invoice_due_date),
  MINUTE(invoice_due_date),
  SECOND(invoice_due_date),
  DAYOFWEEK(invoice_due_date),
  QUARTER(invoice_due_date),
  DAYOFYEAR(invoice_due_date),
  WEEK(invoice_due_date),
  LAST_DAY(invoice_due_date),
  DAYNAME(invoice_due_date),
  MONTHNAME(invoice_due_date)
FROM invoices
Displaying records 1 - 15
invoice_due_date DAYOFMONTH(invoice_due_date) MONTH(invoice_due_date) YEAR(invoice_due_date) HOUR(invoice_due_date) MINUTE(invoice_due_date) SECOND(invoice_due_date) DAYOFWEEK(invoice_due_date) QUARTER(invoice_due_date) DAYOFYEAR(invoice_due_date) WEEK(invoice_due_date) LAST_DAY(invoice_due_date) DAYNAME(invoice_due_date) MONTHNAME(invoice_due_date)
2022-05-08 8 5 2022 0 0 0 1 2 128 19 2022-05-31 Sunday May
2022-05-10 10 5 2022 0 0 0 3 2 130 19 2022-05-31 Tuesday May
2022-05-13 13 5 2022 0 0 0 6 2 133 19 2022-05-31 Friday May
2022-05-16 16 5 2022 0 0 0 2 2 136 20 2022-05-31 Monday May
2022-05-16 16 5 2022 0 0 0 2 2 136 20 2022-05-31 Monday May
2022-05-16 16 5 2022 0 0 0 2 2 136 20 2022-05-31 Monday May
2022-05-21 21 5 2022 0 0 0 7 2 141 20 2022-05-31 Saturday May
2022-05-04 4 5 2022 0 0 0 4 2 124 18 2022-05-31 Wednesday May
2022-05-24 24 5 2022 0 0 0 3 2 144 21 2022-05-31 Tuesday May
2022-05-24 24 5 2022 0 0 0 3 2 144 21 2022-05-31 Tuesday May
2022-05-25 25 5 2022 0 0 0 4 2 145 21 2022-05-31 Wednesday May
2022-05-16 16 5 2022 0 0 0 2 2 136 20 2022-05-31 Monday May
2022-05-20 20 5 2022 0 0 0 6 2 140 20 2022-05-31 Friday May
2022-06-10 10 6 2022 0 0 0 6 2 161 23 2022-06-30 Friday June
2022-06-02 2 6 2022 0 0 0 5 2 153 22 2022-06-30 Thursday June

Format Date and Time

For all different time formatting details in MySQL, see Oracle MySQL documentation .

SELECT
  invoice_due_date,
  DATE_FORMAT(invoice_due_date, '%m') AS MONTH_ONLY,
  DATE_FORMAT(invoice_due_date, '%M') AS MONTH_NAME,
  DATE_FORMAT(invoice_due_date, '%b-%d-%Y') AS MMM_DD_YYYY
FROM invoices
Displaying records 1 - 15
invoice_due_date MONTH_ONLY MONTH_NAME MMM_DD_YYYY
2022-05-08 05 May May-08-2022
2022-05-10 05 May May-10-2022
2022-05-13 05 May May-13-2022
2022-05-16 05 May May-16-2022
2022-05-16 05 May May-16-2022
2022-05-16 05 May May-16-2022
2022-05-21 05 May May-21-2022
2022-05-04 05 May May-04-2022
2022-05-24 05 May May-24-2022
2022-05-24 05 May May-24-2022
2022-05-25 05 May May-25-2022
2022-05-16 05 May May-16-2022
2022-05-20 05 May May-20-2022
2022-06-10 06 June Jun-10-2022
2022-06-02 06 June Jun-02-2022

Date Time Calculation

SELECT
  invoice_due_date,
  payment_date,
  DATE_ADD(invoice_due_date,INTERVAL 9 MONTH),
  DATE_SUB(invoice_due_date,INTERVAL 5 DAY),
  DATEDIFF(invoice_due_date, payment_date),
  -- very useful to ensure the calculation results in days
  -- by default, it is calculating number of days passed by between the specified date and year 0
  TO_DAYS(invoice_due_date)
FROM invoices
ORDER BY invoice_due_date
Displaying records 1 - 15
invoice_due_date payment_date DATE_ADD(invoice_due_date,INTERVAL 9 MONTH) DATE_SUB(invoice_due_date,INTERVAL 5 DAY) DATEDIFF(invoice_due_date, payment_date) TO_DAYS(invoice_due_date)
2022-05-04 2022-05-01 2023-02-04 2022-04-29 3 738644
2022-05-08 2022-05-07 2023-02-08 2022-05-03 1 738648
2022-05-10 2022-05-14 2023-02-10 2022-05-05 -4 738650
2022-05-13 2022-05-09 2023-02-13 2022-05-08 4 738653
2022-05-16 2022-05-12 2023-02-16 2022-05-11 4 738656
2022-05-16 2022-05-11 2023-02-16 2022-05-11 5 738656
2022-05-16 2022-05-21 2023-02-16 2022-05-11 -5 738656
2022-05-16 2022-05-13 2023-02-16 2022-05-11 3 738656
2022-05-17 2022-05-19 2023-02-17 2022-05-12 -2 738657
2022-05-20 2022-05-23 2023-02-20 2022-05-15 -3 738660
2022-05-21 2022-05-22 2023-02-21 2022-05-16 -1 738661
2022-05-23 2022-05-22 2023-02-23 2022-05-18 1 738663
2022-05-24 2022-05-21 2023-02-24 2022-05-19 3 738664
2022-05-24 2022-05-22 2023-02-24 2022-05-19 2 738664
2022-05-25 2022-05-20 2023-02-25 2022-05-20 5 738665

TIME_TO_SEC(): Returns the time argument, converted to seconds. Returns NULL if time is NULL.

SELECT 
  NOW(),
  HOUR(NOW()) * 3600 + MINUTE(NOW()) * 60 + SECOND(NOW()),
  -- very useful to ensure the calculation results in seconds
  TIME_TO_SEC(NOW())
1 records
NOW() HOUR(NOW()) * 3600 + MINUTE(NOW()) * 60 + SECOND(NOW()) TIME_TO_SEC(NOW())
2024-11-09 13:53:25 50005 50005

Search Date and Time

General process:

  1. Specify conditions in WHERE clause;
  2. Convert/Extract date/time field to a particular format that suits your search need;
  3. Use convered/extracted values to search for targeted date/time range.

For example, we would like to search for all invoices with invoice date during May 2022.

SELECT * 
FROM invoices
WHERE invoice_date BETWEEN '2022-05-01' AND '2022-05-31'
ORDER BY invoice_date DESC
Displaying records 1 - 15
invoice_id vendor_id invoice_number invoice_date invoice_total payment_total credit_total terms_id invoice_due_date payment_date
41 123 963253255 2022-05-31 53.75 53.75 0 3 2022-06-30 2022-06-27
42 123 94007069 2022-05-31 400.00 400.00 0 3 2022-06-30 2022-07-01
40 121 97/503 2022-05-30 639.77 639.77 0 3 2022-06-29 2022-06-25
39 110 0-2058 2022-05-28 37966.19 37966.19 0 3 2022-06-27 2022-06-30
38 123 963253272 2022-05-26 61.50 61.50 0 3 2022-06-25 2022-06-30
35 107 RTR-72-3662-X 2022-05-25 1600.00 1600.00 0 4 2022-07-04 2022-07-09
36 121 97/465 2022-05-25 565.15 565.15 0 3 2022-06-24 2022-06-24
37 123 963253260 2022-05-25 36.00 36.00 0 3 2022-06-24 2022-06-26
33 105 94007005 2022-05-23 220.00 220.00 0 3 2022-06-22 2022-06-26
34 123 963253232 2022-05-23 127.75 127.75 0 3 2022-06-22 2022-06-18
31 104 P02-3772 2022-05-21 7125.34 7125.34 0 3 2022-06-20 2022-06-24
32 121 97/486 2022-05-21 953.10 953.10 0 3 2022-06-20 2022-06-22
30 123 1-200-5164 2022-05-20 63.40 63.40 0 3 2022-06-19 2022-06-24
29 108 121897 2022-05-19 450.00 450.00 0 4 2022-06-28 2022-07-03
28 123 963253263 2022-05-16 109.50 109.50 0 3 2022-06-15 2022-06-10

Or, alternatively:

SELECT * 
FROM invoices
WHERE MONTH(invoice_date) = 5 AND YEAR(invoice_date) = 2022
ORDER BY invoice_date DESC
Displaying records 1 - 15
invoice_id vendor_id invoice_number invoice_date invoice_total payment_total credit_total terms_id invoice_due_date payment_date
41 123 963253255 2022-05-31 53.75 53.75 0 3 2022-06-30 2022-06-27
42 123 94007069 2022-05-31 400.00 400.00 0 3 2022-06-30 2022-07-01
40 121 97/503 2022-05-30 639.77 639.77 0 3 2022-06-29 2022-06-25
39 110 0-2058 2022-05-28 37966.19 37966.19 0 3 2022-06-27 2022-06-30
38 123 963253272 2022-05-26 61.50 61.50 0 3 2022-06-25 2022-06-30
35 107 RTR-72-3662-X 2022-05-25 1600.00 1600.00 0 4 2022-07-04 2022-07-09
36 121 97/465 2022-05-25 565.15 565.15 0 3 2022-06-24 2022-06-24
37 123 963253260 2022-05-25 36.00 36.00 0 3 2022-06-24 2022-06-26
33 105 94007005 2022-05-23 220.00 220.00 0 3 2022-06-22 2022-06-26
34 123 963253232 2022-05-23 127.75 127.75 0 3 2022-06-22 2022-06-18
31 104 P02-3772 2022-05-21 7125.34 7125.34 0 3 2022-06-20 2022-06-24
32 121 97/486 2022-05-21 953.10 953.10 0 3 2022-06-20 2022-06-22
30 123 1-200-5164 2022-05-20 63.40 63.40 0 3 2022-06-19 2022-06-24
29 108 121897 2022-05-19 450.00 450.00 0 4 2022-06-28 2022-07-03
28 123 963253263 2022-05-16 109.50 109.50 0 3 2022-06-15 2022-06-10